ALTER function fn_load_line_item_details(@sales_order_id int)
returns  @fn_result Table(SalesOrder_ID int,
			LineItem_ID int,
			COOL_Line_Item_No int,
			Product_ID int,
			COOL_Qty int,
			COOL_Unit_Cost float,
			COOL_Amount float,
			COOL_Requested_Date datetime,
			COOL_Sugg_Schedule_Date_With_Substitutables datetime,
			COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
			COOL_Schedule_Date datetime,
			COOL_Revised_Date datetime,
			COOL_Status varchar(50),
			COOL_Taxable int,
			SalesOrder_No varchar(100),
			COOL_Description varchar(200),
			Shipped_qty int,
			Customer_PartNumber varchar(50),
			Label_Number varchar(50))
as
begin
	declare @temp_sales_order_id int
	declare @line_item_id int
	declare @line_item_no int
	declare @product_id int
	declare @req_qty int
	declare @unit_cost float
	declare @amount float
	declare @request_date datetime
	declare @sugg_date_with datetime
	declare @sugg_date_without datetime
	declare @schedule_date datetime
	declare @revised_date datetime
	declare @status varchar(50)
	declare @taxable int
	declare @sales_order_no varchar(100)
	declare @description varchar(200)
	declare @shipped_qty int	
	declare @customer_partnumber varchar(50)
	declare @customer_id int
	declare @label_number varchar(50)
	declare @odm_id int 
	declare @backlog_orders_cursor CURSOR
	declare @result Table(SalesOrder_ID int,
			LineItem_ID int,
			COOL_Line_Item_No int,
			Product_ID int,
			COOL_Qty int,
			COOL_Unit_Cost float,
			COOL_Amount float,
			COOL_Requested_Date datetime,
			COOL_Sugg_Schedule_Date_With_Substitutables datetime,
			COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
			COOL_Schedule_Date datetime,
			COOL_Revised_Date datetime,
			COOL_Status varchar(50),
			COOL_Taxable int,
			SalesOrder_No varchar(100),
			COOL_Description varchar(200),
			Shipped_Qty int,
			Customer_PartNumber varchar(50),
			Label_Number varchar(50))
			
	SELECT @customer_id = Customer_Id, @odm_id = SO_Odm_Id from Sales_Order where SalesOrder_Id = @sales_order_id
	set @backlog_orders_cursor = CURSOR FOR
	SELECT Sales_Order.SalesOrder_ID, LineItem_ID,COOL_Line_Item_No,Product_ID,
		COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date,
		COOL_Sugg_Schedule_Date_With_Substitutables,
		COOL_Sugg_Schedule_Date_Without_Substitutables,
		COOL_Schedule_Date, COOL_Revised_Date, COOL_Status,
		COOL_Taxable, SalesOrder_No, COOL_Description, COOL_CUSTOMER_PARTNUMBER
		FROM Customer_Open_Order_LineItem,
		Sales_Order WHERE Sales_Order.SalesOrder_ID = @sales_order_id and 
		sales_order.salesOrder_id = Customer_Open_Order_LineItem.salesOrder_id		
	begin
		open @backlog_orders_cursor		
		fetch next from @backlog_orders_cursor into 
			@temp_sales_order_id, @line_item_id, @line_item_no,
			@product_id,@req_qty,@unit_cost, @amount, @request_date,
			@sugg_date_with, @sugg_date_without, @schedule_date,
			@revised_date, @status, @taxable, @sales_order_no,
			@description, @customer_partnumber
		while @@fetch_status = 0
		begin		
			set @shipped_qty = 0		
			select @shipped_qty = SUM(CSLIT_Qty_Shipped) FROM 
			Customer_Shipping_Line_Item_Table Where LineItem_ID = @line_Item_id and  CSLIT_Status = 
			'SHIPPED' group by LineItem_ID
			if(@shipped_qty is null)
				set @shipped_qty = 0
	
				if(@shipped_qty = @req_qty)
					set @status = 'SHIPPED'
				if((@shipped_qty <> 0 ) and (@shipped_qty < @req_qty))
					set @status = 'PARTIALLY SHIPPED'
				
			insert @result values(@temp_sales_order_id,@line_item_id,
				@line_item_no, @product_id, @req_qty, @unit_cost, @amount,
				@request_date, @sugg_date_with, @sugg_date_without,
				@schedule_date,@revised_date,@status,@taxable,
				@sales_order_no, @description,@shipped_qty,@customer_partnumber,@label_number)
			
			fetch next from @backlog_orders_cursor into 
				@temp_sales_order_id, @line_item_id, @line_item_no,
				@product_id,@req_qty,@unit_cost, @amount, @request_date,
				@sugg_date_with, @sugg_date_without, @schedule_date,
				@revised_date, @status, @taxable, @sales_order_no,
				@description, @customer_partnumber
		end
		
		close @backlog_orders_cursor
		deallocate @backlog_orders_cursor
	end
	insert @fn_result select * from @result
	return
end

